<?php

/*
 * To change this template, choose Tools | Templates
* and open the template in the editor.
*/

class HT_Model_administrator_models_seogroup extends Zend_Db_Table {//ten class fai viet hoa

	protected $_db;

	public function __construct() {
		$this->_name = "seo_group";
		$this->_db = Zend_Registry::get('dbMain');
		parent::init();
	}
	
	public function checkExistsGroup($groupId){
		$sql = "SELECT COUNT(group_id) FROM seo_group WHERE active = 1 AND is_deleted = 0 AND group_id = ".(int)$groupId;
		return $this->_db->fetchOne($sql);
	}
	
	public function getRandomGroupId(){
		$sql = "SELECT group_id FROM seo_group WHERE active = 1";
		$idList = $this->_db->fetchAll($sql);
		$index = rand(0,sizeof($idList)-1);
		return $idList[$index]['group_id'];
	}
	
	public function getRandomListGroup($limit){
		$sql 	= "SELECT * FROM seo_group WHERE active = 1";
		$groups = $this->_db->fetchAll($sql);
		$ids 	= array();
		$groupList = array();
		if(sizeof($groups) <= $limit) $limit = sizeof($groups);
		while(sizeof($groupList) < $limit){
			$index 	= rand(0,sizeof($groups)-1);
			$groupId = $groups[$index]['group_id'];
			if(!in_array($groupId,$ids)){
				$ids[] = $groupId;
				$groupList[] = $groups[$index];
			}
		}
		return $groupList;
	}
	
	public function getLatestGroup($limit){
		$sql = " SELECT * FROM seo_group WHERE active= 1 LIMIT $limit";
		return $this->_db->fetchAll($sql);
	}
	
	public function suggestGroup($limit = 1000,$target = 5){
		$keylistOK 	= array();
		$info 		= $this->getAllKeywords($limit);
		$keywords 	= $info['keywords'];
		$ids 		= $info['ids'];
		if(is_array($keywords) && sizeof($keywords) >0){
			$this->doSuggest($keywords,$target);
			if(sizeof($keywords) == $limit){
				$this->updateIsGroupCheck(1,$ids);
			}else{
				$this->updateIsGroupCheck(0);
			}
			
		}else{
			$this->updateIsGroupCheck(0);
		}
		return 'done';
	}
	
	private function doSuggest($keywords,$target){
		foreach((array)$keywords as $keyword){
			$keyword = strtolower(trim($keyword));
			$totalMark = $this->countKeywordInNews($keyword,$target);
			if($totalMark >0){
				$groupId = $this->checkExistsKeyword($keyword);
				if($groupId >0){
					$data = array('total_mark'=>$totalMark,'date_updated'=>date('Y-m-d H:i:s'));
					$this->update($data, "group_id=".(int)$groupId);
				}else{
					$data = array('keyword'=>$keyword,'total_mark'=>$totalMark,'date_created'=>date('Y-m-d H:i:s'),'date_updated'=>date('Y-m-d H:i:s'));
					$this->insert($data);
				}
			}
		}
	}
	
	function updateIsGroupCheck($status,$ids = array()){
		if($status == 1){
			$sql = "UPDATE seo_page SET is_group_check = 1 WHERE seopage_id IN (".implode(',', $ids).")";
		}else{
			$sql = "UPDATE seo_page SET is_group_check = 0";
		}
		return $this->_db->query($sql);
	}
	
	
	function countKeywordInNews($keyword,$target = 5){
		$keyword = strtolower($keyword);
		$sql = "SELECT id_news newsId, title_vn title FROM news WHERE title_vn LIKE '%$keyword%' AND `status` = 'active'";
		$newsList = $this->_db->fetchAll($sql);
		$count = 0;
		foreach((array)$newsList as $news){
			$newsId = $news['newsId'];
			$title = strtolower(trim($news['title']));
			if(strpos($title, $keyword) !== false) $count++;
		}
		if($count >= $target){
			return $count;
		}else{
			return false;
		}
	}
	
	private function getAllKeywords($limit){
		$keywords = array();
		$sql = "SELECT seopage_id,tag_title,secondary_keyword
				FROM seo_page
				WHERE is_group_check = 0
				ORDER BY last_updated DESC LIMIT $limit";
		$keyList = $this->_db->fetchAll($sql);
		$ids = array();
		foreach((array)$keyList as $key){
			$tag_title 			= strtolower(trim($key['tag_title']));
			$sk 				= strtolower(trim($key['secondary_keyword']));
			$id 				= trim($key['seopage_id']);
			
			if($tag_title && !in_array($tag_title,$keywords)) $keywords[] = $tag_title;
			if($sk && !in_array($sk,$keywords)) $keywords[] = $sk;
			if($id && !in_array($id,$ids)) $ids[] = $id;
		}
		
		return array('keywords'=>$keywords,'ids'=>$ids);
	}
	
	public function autoLinkContent($limit){
		@$auth 			= Zend_Auth::getInstance();
		$user 			= @$auth->getStorage()->read();
		$wishid			= $user->wishid;
		$countLinked = 0;
		$noLinks = $this->getNoLinkSeogroup($limit);
		foreach((array)$noLinks as $seo){
			$groupId 		= $seo['group_id'];
			$keyword1 		= $seo['tag_title'];
			$keyword2 		= $seo['secondary_keyword'];
			$title 			= trim($seo['title']);
			$news 			= $this->getNewsIdForLink($keyword1);
			if(!is_array($news)) $news = $this->getNewsIdForLink($keyword2);
				
			$referenceId	= $news['id_news'];
			$newsTitle		= $news['title_vn'];
				
			if(!$title) $title = $keyword1;
				
			$data 			= array();
			$data['is_autolink'] 	   = 1;
			$data['link_type_id'] 	   = 2;
			if($referenceId >0){
				$countLinked++;
				$data['reference_news_id'] = $referenceId;
				$data['tag_description']   = $this->getSeoAutoContent($keyword1,1);
				$data['title']   		   = $title;
				$data['admin_update_id']   = $wishid;
				$data['date_updated'] 	   = date('Y-m-d H:i:s');
			}
			$this->update($data, 'group_id = '.$groupId);
		}
		return $countLinked;
	}
	
	public function getSeoAutoContent($keyword,$autoType){
		/*
		 * $autoType = 1 -> Tag description
		* $autoType = 2 -> Trích dẫn ngắn của tin
		* $autoType = 3 -> Comment của tin
		* */
		$sql 		= "SELECT auto_id,auto_content	FROM seo_auto	WHERE  auto_type_id = ?";
		$autoList 	= $this->_db->fetchAll($sql,array($autoType));
		$index 		= rand(0,sizeof($autoList)-1);
		$autoContent = $autoList[$index]['auto_content'];
		$autoContent = str_replace('__keyword__', $keyword, $autoContent);
		return $autoContent;
	}
	
	public function getNewsIdForLink($keyword){
		$keyword = strtolower($keyword);
		// Lay tat ca cac tin co title gan giong tu khoa
		$sql = "SELECT id_news,title_vn FROM news WHERE `status` = 'active' AND title_vn LIKE '%$keyword%'";
		$newsList = $this->_db->fetchAll($sql);
	
		$arrIds = array();
		$arrNews = array();
		// Kiem tra toan bo danh sach tin
		foreach((array)$newsList as $news){
			$title 			= strtolower($news['title_vn']);
			$id 			= $news['id_news'];
			if(@strpos($title,$keyword) !== false && !in_array($id, $arrIds)){
				$arrIds[] = $id;
				$arrNews[] = $news;
			}
		}
		//print_r($newsList);
		if(is_array($arrNews) && sizeof($arrNews) >0){
			return $arrNews[0];
		}else{
			return 0;
		}
	}
	
	public function getNoLinkSeogroup($limit){
		/**
		 * Dieu kien xac dinh chua ket noi
		 * reference_news_id = 0 -> Chua ket noi voi bai viet nao
		 * is_keyword = 1 -> La tu khoa
		 * priority = 1 -> Da duoc set do uu tien bang tay, admin da kiem duyet la tu khoa roi.
		 *
		 */
		$sql = "SELECT group_id,tag_title,secondary_keyword,title
		FROM seo_page
		WHERE  reference_news_id = 0 AND is_keyword = 1 AND priority = 1
		ORDER BY monthly_search DESC LIMIT $limit";
		return $this->_db->fetchAll($sql);
	}
	
	public function setKeywords($isKeyword,$idList){
		$sql = "UPDATE seo_page set is_keyword = ".(int)$isKeyword." WHERE group_id IN (".implode(',', $idList).")";
		$this->_db->query($sql);
	}
	
	public function setSeoDatas($seoDatas){
		foreach($seoDatas as $item){
			$keyword 	= trim($item['secondary_keyword']);
			$tags	 	= trim($item['seo_tags']);
			$groupId 	= $item['group_id'];
			$data = array('secondary_keyword'=>$keyword,'seo_tags'=>$tags);
			if($keyword || $tags) $data['priority'] = 1;
			$this->update($data, 'group_id='.$groupId);
		}
		return 1;
	}
	
	public function setPriority($priority,$idList){
		$sql = "UPDATE seo_page set priority = ".(int)$priority." WHERE group_id IN (".implode(',', $idList).")";
		$this->_db->query($sql);
	}
	
	public function getTodaySeoGroup(){
		$sql = " SELECT * FROM seo_group WHERE active = 1 AND date(last_update) = date('".date('Y-m-d')."')";
		return $this->_db->fetchAll($sql);
	}
	
	public function updateKeywords($keywords){
		foreach((array)$keywords as $item){
			$keyword 		= trim($item[0]);
			$competition 	= trim($item[1]);
			$monthlySearch 	= trim($item[2]);
			$data 						= array();
			$data['tag_title'] 			= $keyword;
			$data['competition'] 		= $competition;
			$data['monthly_search'] 	= $monthlySearch;
			$groupId 					= $this->checkExistsKeyword($keyword);
			if($groupId > 0){
				$this->update($data, "group_id = ".(int)$groupId);
			}else{
				$this->insert($data);
			}
		}
		return 1;
	}

	public function updateData($data,$groupId){
		$keyword = trim($data['keyword']);
		if($keyword != '' && !$this->checkExistsKeyword($keyword,'update',$groupId)){
			$this->update($data, "group_id=".(int)$groupId);
			return $groupId;
		}else{
			return "-1";
		}
	}
	
	public function addData($data){
		$keyword = trim($data['keyword']);
		if($keyword != '' && !$this->checkExistsKeyword($keyword)){
			$this->insert($data);
			return $this->getMaxId();
		}else{
			return "-1";
		}
	}
	
	public function checkExistsKeyword($keyword,$action="insert",$groupId = null){
		$keyword = strtolower(trim($keyword));
		if($action == 'insert'){
			$sql = "SELECT group_id FROM seo_group WHERE keyword REGEXP BINARY '^$keyword$' LIMIT 1";
			return  (int)$this->_db->fetchOne($sql);
		}else{
			$sql = "SELECT group_id FROM seo_group WHERE keyword REGEXP BINARY '^$keyword$' AND group_id <> ?  LIMIT 1";
			return  (int)$this->_db->fetchOne($sql,array($groupId));
		}
	}
	
	public function getMaxId(){
		$sql = "SELECT MAX(group_id) FROM seo_group";
		return  (int)$this->_db->fetchOne($sql);
	}
	public function getSeogroup($groupId,$filter = array()) {
		$sql = " SELECT * FROM seo_group WHERE group_id= ".(int)$groupId;
		$seoGroup = $this->_db->fetchRow($sql);
		if(isset($filter['getRelatedNews'])) $seoGroup['relatedNews'] = $this->getRelatedNews($seoGroup,100);
		return $seoGroup;
	}
	
	private function getOtherSeoNews($limit){
		$arrIds = $this->getSeoPageIds($limit);
		$sql = "SELECT * FROM seo_page WHERE reference_news_id >0 AND group_id in (".implode(',',$arrIds).")";
		return $this->_db->fetchAll($sql);
	}
	
	public function getSeoPageIds($limit){
		$sql = "SELECT group_id FROM seo_page WHERE reference_news_id >0";
		$ids = $this->_db->fetchAll($sql);
		$arrIds = array();
		while(sizeof($arrIds) < $limit){
			$index = rand(0,sizeof($ids) - 1);
			if(!in_array($ids[$index]['group_id'], $arrIds)) $arrIds[] = $ids[$index]['group_id'];
		}
		//echo "<pre>";
		//print_r($arrIds);die();
		return $arrIds;
	}
	
	private function getRelatedNews($seoData,$limit = 10){
		$keywords 				= array();
		$secondaryKeyword  		= trim($seoData['secondary_keyword']);
		if($secondaryKeyword) $keywords 	= explode(',', $secondaryKeyword);
		$keywords[] 			= $seoData['keyword'];
		$sqlLike = null;
		foreach((array)$keywords as $keyword){
			$sqlLike .= " title_vn LIKE '%$keyword%' OR ";
		}
		if($sqlLike){
			$sqlLike = trim(substr($sqlLike, 0, -4));
			$sqlLike = " AND ($sqlLike)";
		} 
		
		$sql = " SELECT * FROM news WHERE 1=1 AND status = 'active' $sqlLike LIMIT ".(int)$limit;
		$newsList = $this->_db->fetchAll($sql);
	
		$arrIds = array();
		$arrNews = array();
		$i = 0;
		foreach($newsList as $news){
			$title 	= $news['title_vn'];
			$id 	= $news['id_news'];
			$title	= strtolower($title);
			foreach((array)$keywords as $keyword){
				$keyword = strtolower($keyword);
				if(strpos($title,$keyword) !== false && !in_array($id, $arrIds)){
					$i++;
					if($i <= $limit){
						$arrNews[] = $news;
						$arrIds[] = $id;
					}
				}
			}
		}
		return $arrNews;
	}
	
	
	public function getSeogroupByTagTitle($url) {
		$sql = " SELECT * FROM seo_page WHERE tag_title = ? LIMIT 1 ";
		return $this->_db->fetchRow($sql,array($url));
	}
	
	public function getLatestLink($showId = 2,$limit = 20){
		$sql = "SELECT * FROM seo_page WHERE is_keyword = 1 AND reference_news_id > 0 ORDER BY date_updated DESC LIMIT ".(int)$limit;
		return $this->_db->fetchAll($sql);
	}
	
	public function getListSeogroup_nb($filter = array()) {
		$sqlPlus = $this->getListSeogroup_sqlPlus($filter);
		$sql = "SELECT COUNT(sg.group_id)
				FROM seo_group sg
				WHERE 1=1 $sqlPlus";
		return $this->_db->fetchOne($sql);
	}
	
	public function getListSeogroup($start=0,$size = 10,$filter = array()) {
		$sqlPlus = $this->getListSeogroup_sqlPlus($filter);
		$sqlOrder = $this->getListSeogroup_orderBy($filter);
		$sql = "SELECT sg.*
				FROM seo_group sg
				WHERE 1=1 $sqlPlus $sqlOrder LIMIT $start,$size";
		return $this->_db->fetchAll($sql);
	}
	
	private function getListSeogroup_orderBy($filter){
		$orderBy 	= trim(@$filter['orderBy']);
		$direction 	= trim(@$filter['direction']);
		if($orderBy && $direction){
			$sqlOrder 	= " ORDER BY sg.$orderBy $direction ";
		}else{
			$sqlOrder 	= " ORDER BY sg.active ASC, sg.total_mark DESC ";
		}
		return $sqlOrder;
	}
	
	private function getListSeogroup_sqlPlus($filter){
		$sqlPlus = null;
		foreach((array)$filter as $key => $val){
			$key = trim($key);
			$val = trim($val);
			switch($key){
				case 'keyword':
					if($val){
						$sqlPlus .= " AND (sg.subject LIKE '%$val%' OR sg.keyword LIKE '%$val%') ";
					}
					break;
				case 'status':
					if($val){
						$sqlPlus .= " AND sg.status = ".(int)$val;
					}
					break;
				case 'is_deleted':
					if((int)$val == 0 || (int)$val == 1){
						$sqlPlus .= " AND sg.is_deleted = ".(int)$val;
					}
					break;
						
			}
		}
		return $sqlPlus;
	}
	
	
	
	// For news
	
	public function getListNews_nb($filter = array()) {
		$sqlPlus 	= $this->getListNews_sqlPlus($filter);
		$sqlJoin	= null;
		$searchFor  = trim(@$filter['search_for']);
		if($searchFor == 'connected'){
			$sqlJoin = ' INNER JOIN seo_group sg ON sg.reference_news_id = news.id_news';
		}
		$sql 		= "SELECT news.id_news
		FROM news $sqlJoin
		WHERE news.status='active' $sqlPlus GROUP BY news.id_news ";
		$idList = $this->_db->fetchAll($sql);
		return sizeof($idList);
	}
	
	public function getListNews($start=0,$size = 10,$filter = array()) {
		$sqlPlus 	= $this->getListNews_sqlPlus($filter);
		$sqlJoin	= null;
		$searchFor  = trim(@$filter['search_for']);
		if($searchFor == 'connected'){
			$sqlJoin = ' INNER JOIN seo_group sg ON sg.reference_news_id = news.id_news';
		}
		$sql 		= "SELECT news.id_news newsId,news.title_vn newsTitle, news.desc_vn description,news.pictrue, news.created
		FROM news
		$sqlJoin
		WHERE news.status='active' $sqlPlus GROUP BY news.id_news ORDER BY news.created DESC LIMIT $start,$size";
		return $this->_db->fetchAll($sql);
	}
	
	private function getListNews_sqlPlus($filter){
		$sqlPlus = null;
		foreach((array)$filter as $key => $val){
			$key = trim($key);
			$val = trim($val);
			switch($key){
				case 'denied_ids':
					if($val){
						$sqlPlus .= " AND news.id_news NOT IN($val)";
					}
					break;
				case 'keyword':
					if($val){
						$sqlPlus .= " AND (news.title_vn LIKE '%$val%') ";
					}
					break;
				case 'search_for':
					if($val == 'not_connected'){
						$sqlPlus .= " AND news.id_news NOT IN (SELECT reference_news_id FROM seo_page)";
					}
					break;
			}
		}
		return $sqlPlus;
	}
	
}

?>
